﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using Vbyte.SharpOrm.SqlDb;
using System.Data.SqlClient;

namespace Vbyte.SharpOrm.Adapter
{
    /// <summary>
    /// 针对SQLServer的适配
    /// </summary>
    public class SqlServerAdapter : ISqlDbAdapter
    {
        /// <summary>
        /// Sql数据类型映射词典
        /// </summary>
        private static Dictionary<string, string> SqlDbTypeMapDict = new Dictionary<string, string>();

        /// <summary>
        /// Initializes a new instance of the <see cref="SqlServerAdapter"/> class.
        /// </summary>
        public SqlServerAdapter()
        {
            InitializeDict(); 
        }

        private void InitializeDict()
        {
            if (SqlDbTypeMapDict.Count == 0)
            {
                SqlDbTypeMapDict.Add(typeof(Single).FullName, "float");
                SqlDbTypeMapDict.Add(typeof(double).FullName, "real");
                SqlDbTypeMapDict.Add(typeof(decimal).FullName, "decimal");
                SqlDbTypeMapDict.Add(typeof(long).FullName, "bigint");
                SqlDbTypeMapDict.Add(typeof(int).FullName, "int");
                SqlDbTypeMapDict.Add(typeof(short).FullName, "smallint");
                SqlDbTypeMapDict.Add(typeof(byte).FullName, "smallint");
                SqlDbTypeMapDict.Add(typeof(Guid).FullName, "uniqueidentifier");
                SqlDbTypeMapDict.Add(typeof(bool).FullName, "bit");
                SqlDbTypeMapDict.Add(typeof(DateTime).FullName, "datetime");
                SqlDbTypeMapDict.Add(typeof(byte[]).FullName, "image");
            }
        }

        #region ISqlDbAdapter 成员

        /// <summary>
        /// 获取针对NET CLR基本数据类型的Sql数据类型转换
        /// </summary>
        /// <param name="clrType">NET CLR基本基本类型</param>
        /// <param name="maxLength">可为空的最大长度限制</param>
        /// <returns></returns>
        public string GetNativeType(Type clrType, int? maxLength)
        {
            string strSqlType = "nvarchar(4000)";
            if (clrType == typeof(string))
            {
                if (maxLength.HasValue)
                {
                    if (maxLength.Value == int.MaxValue)
                    {
                        strSqlType = "nvarchar(Max)";
                    }
                    else
                    {
                        strSqlType = "nvarchar(" + maxLength.Value.ToString() + ")";
                    }
                }
            }
            else if (clrType.IsGenericType 
                && clrType.GetGenericTypeDefinition() == typeof(Nullable<>).GetGenericTypeDefinition())
            {
                string keyType = Nullable.GetUnderlyingType(clrType).FullName;
                if (SqlDbTypeMapDict.ContainsKey(keyType))
                {
                    strSqlType = SqlDbTypeMapDict[keyType];
                }
            }
            else
            {
                if (SqlDbTypeMapDict.ContainsKey(clrType.FullName))
                {
                    strSqlType = SqlDbTypeMapDict[clrType.FullName];
                }
            }
            return strSqlType;
        }

        /// <summary>
        /// 获取指定表内最新产生标志的SQL语句获取文本
        /// </summary>
        /// <param name="currentTabName">当前表名，如果为null或空则返回全局标志。</param>
        /// <returns>标志的SQL语句获取文本</returns>
        public string GetIdentitySql(string currentTabName)
        {
            /*
               当运行完插入语句后，执行select @@identity就可得到自动生成的id
               如果是sql server 最好用select SCOPE_IDENTITY() as id
               因为@@identity全局的
               同类还有IDENT_CURRENT（‘table’）
               IDENT_CURRENT 返回为任何会话和任何作用域中的特定表最后生成的标识值。IDENT_CURRENT 不受作用域和会话的限制，而受限于指定的表。IDENT_CURRENT 返回为任何会话和作用域中的特定表所生成的值。
               @@IDENTITY 返回为当前会话的所有作用域中的任何表最后生成的标识值。
               SCOPE_IDENTITY 返回为当前会话和当前作用域中的任何表最后生成的标识值

               SCOPE_IDENTITY 和 @@IDENTITY 返回在当前会话中的任何表内所生成的最后一个标识值。但是，SCOPE_IDENTITY 只返回插入到当前作用域中的值；@@IDENTITY 不受限于特定的作用域。
               */
            return string.IsNullOrEmpty(currentTabName) ? "select @@identity as id"
                : "select IDENT_CURRENT('" + currentTabName + "') as id";
        }

        /// <summary>
        /// 标志该数据适配器是否有自己的数据分页实现
        /// </summary>
        /// <returns>实现则为true，否则为false。</returns>
        public bool HasImplementCustomPage()
        {
            return true;
        }

        /// <summary>
        /// 获取满足实例条件的分页数据
        /// </summary>
        /// <param name="dbInstance">数据库辅助实例</param>
        /// <param name="builderInstance">在实例样本上产生的SQL构建适配器</param>
        /// <param name="tabDef">获取实例表定义（备用:获取主键、max函数运算信息）</param>
        /// <param name="currentPage">当前数据页页次</param>
        /// <param name="pageSize">每页显示记录条数</param>
        /// <param name="sharedOpenConnection">当前存在的已打开连接的共享数据库连接</param>
        /// <param name="command">已生成的数据库操作命令实例</param>
        /// <returns>满足条件的内容数据表</returns>
        public DataTable GetPagedDataTable(ISqlDb dbInstance, ISqlBuilderAdapter builderInstance,
            SqlTable tabDef, int currentPage, int pageSize,
            DbConnection sharedOpenConnection, DbCommand command)
        {
            SqlServer Svr = (SqlServer)dbInstance;
            string cmdSql = string.Empty;

            if (currentPage == 1)
            {
                cmdSql = string.Format("Select Top {0} {1} from {2} {3} {4} {5}",
                    pageSize,
                    string.Join(",", builderInstance.FieldNamesList), //字段列表
                    builderInstance.VirtualTable,                     //数据来源表
                    builderInstance.SqlCondition,                     //条件限制
                    builderInstance.SqlGroupBy,                       //分组
                    builderInstance.SqlOrderBy);                      //排序
            }
            else
            {
                if (Svr.IsSqlServer2000())
                {
                    //使用SQL临时表实现行号
                    cmdSql = string.Format(@"select OrmRowIdx=identity(int,1,1), {1} into {0}  from {2} {3} {4}
select top {5} * from {0} where OrmRowIdx > {6}
drop table {0}",
                        string.Concat("#t", Guid.NewGuid().ToString().Replace("-","")),
                        string.Join(",", builderInstance.FieldNamesList),
                        builderInstance.VirtualTable,
                        builderInstance.SqlCondition,
                        builderInstance.SqlOrderBy,
                        pageSize,
                        (currentPage - 1) * pageSize);
                }
                else
                {
                    string sqlOrder = builderInstance.SqlOrderBy;
                    if (string.IsNullOrEmpty(sqlOrder))
                    {
                        sqlOrder = "Order By " + builderInstance.FieldNamesList[0];
                    }

                    cmdSql = string.Format("Select Top {0} * from ( Select {1}, ROW_NUMBER() Over({2}) as OrmRowIdx from {3} {4}) as T where T.OrmRowIdx > {5} {2}",
                            pageSize,
                            string.Join(",", builderInstance.FieldNamesList),
                            sqlOrder,
                            builderInstance.VirtualTable,
                            builderInstance.SqlCondition,
                            (currentPage-1)*pageSize);
                }
            }
            command.CommandText = cmdSql;
            return Svr.GetDataTable(sharedOpenConnection, command);
        }

        #endregion
    }
}
